﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using Simple.Services.System;
using OfficeOpenXml;
using System.IO;
using Simple.Services.System.QHSEViolationCategory.Models;
using Simple.Services.System.QHSEDanger.Models.AnalysisInput;
using System.Globalization;
using System.Security.Cryptography;
using Simple.Services;
using System.Reflection;
using OfficeOpenXml.Style;

public class QHSEOverLappingDangerService
{
    private readonly SimpleDbContext _context;
    private readonly CacheService _cacheService;
    private readonly IQhseFileService _qhseFileService;


    public QHSEOverLappingDangerService(SimpleDbContext context, CacheService cacheService,IQhseFileService qhseFileService)
    {
        _context = context;
        _cacheService = cacheService;
        _qhseFileService = qhseFileService;
    }
    public async Task<List<QHSEOverLappingDangerModel>> GetBySqlAsync(IdInputModel id)
    {
        var violations = await _context.Set<QHSESysDanger>().FromSqlRaw(id.Id).ToListAsync();
        var models = MapperHelper.Map<List<QHSEOverLappingDangerModel>>(violations);
        return models;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<PageResultModel<QHSEOverLappingDangerModel>> GetPageAsync(OverLappingDangerPageInputModel input)
    {

        var result = new PageResultModel<QHSEOverLappingDangerModel>();
        if (!string.IsNullOrEmpty(input.CheckOrganizationName) || !string.IsNullOrEmpty(input.DangerOrganizationName)
            || !string.IsNullOrEmpty(input.Status) || !string.IsNullOrEmpty(input.DangerType)
            || !string.IsNullOrEmpty(input.UserName) || input.TimeOn != null || input.TimeEnd != null)
        {
                var query = _context.Set<QHSESysOverLappingDanger>().AsNoTracking().AsQueryable();

                // 根据条件查询
                if (!string.IsNullOrEmpty(input.DangerOrganizationName))
                {
                    var org = await _context.Set<QHSESysOrganization>().FindAsync(input.DangerOrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger);
                    }
                }
                if (!string.IsNullOrEmpty(input.UserName))
                {
                    query = query.Where(u => EF.Functions.Like(u.CheckName, $"%{input.UserName}%"));
                }
                if (input.TimeOn != null && input.TimeEnd != null)
                {
                    query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
                }
                if (!string.IsNullOrEmpty(input.DangerType) || !string.IsNullOrEmpty(input.DangerTypeCode))
                {
                    query = query.Where(u => EF.Functions.Like(u.DangerType, $"%{input.DangerType}%") || (u.DangerTypeCode!=null && u.DangerTypeCode == input.DangerTypeCode));
                }
                if (!string.IsNullOrEmpty(input.Status))
                {
                    query = query.Where(u => EF.Functions.Like(u.Status, $"%{input.Status}%"));
                }
                if (!string.IsNullOrEmpty(input.CheckOrganizationName))
            {
                var corg = await _context.Set<QHSESysOrganization>().FindAsync(input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                        .Join(_context.Set<QHSESysUser>(), // Join with User table
                            danger => danger.UserId,
                            user => user.UserId,
                            (danger, user) => new { Danger = danger, User = user })
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                            joinResult => joinResult.User.OrganizationId,
                            organization => organization.OrganizationId,
                            (joinResult, organization) => new { Danger = joinResult.Danger, User = joinResult.User, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                        .Select(joinResult => joinResult.Danger);
                }
            }
                if (input.ContractingUnitId != null)
                {
                    if (input.ContractingUnitId.Count != 0)
                    {
                        query = query.Where(u => string.Join("-", input.ContractingUnitId).Contains(u.ContractingUnitId));
                    }
                }
            // 获取总数量
            var totalRows = await query.CountAsync();

                // 分页查询
                var dangers = await query
                            .OrderBy(u => u.CheckType)
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                danger => danger.OrganizationId,
                                organization => organization.OrganizationId,
                                (danger, organization) => new { Danger = danger, Organization = organization })
                            .Select(joinResult => new QHSEOverLappingDangerModel
                            {
                                qhseXyDangerId = joinResult.Danger.QhseXyDangerId,
                                CheckType = joinResult.Danger.CheckType,
                                UserId = joinResult.Danger.UserId,
                                CheckName = joinResult.Danger.CheckName,
                                CheckDate = joinResult.Danger.CheckDate,
                                OrganizationId = joinResult.Danger.OrganizationId,
                                OrganizationName = joinResult.Organization.NamePath,
                                WorkItem = joinResult.Danger.WorkItem,
                                WorkSpecialty = joinResult.Danger.WorkSpecialty,
                                WorkType = joinResult.Danger.WorkType,
                                WorkContent = joinResult.Danger.WorkContent,
                                WorkRisk = joinResult.Danger.WorkRisk,
                                Location = joinResult.Danger.Location,
                                DangerType = joinResult.Danger.DangerType,
                                Description = joinResult.Danger.Description,
                                DangerCategory = joinResult.Danger.DangerCategory,
                                HseElement = joinResult.Danger.HseElement,
                                ObserveComm = joinResult.Danger.ObserveComm,
                                CauseAnalysis = joinResult.Danger.CauseAnalysis,
                                DirectDepartment = joinResult.Danger.DirectDepartment,
                                PotentialConsequence = joinResult.Danger.PotentialConsequence,
                                DangerPic = joinResult.Danger.DangerPic,
                                ReformerDate = joinResult.Danger.ReformerDate,
                                ReformerPic = joinResult.Danger.ReformerPic,
                                Status = joinResult.Danger.Status,
                                AcceptanceDate = joinResult.Danger.AcceptanceDate,
                                CheckDescription = joinResult.Danger.CheckDescription,
                                ContractingUnitId = joinResult.Danger.ContractingUnitId,
                                ContractingUnitName = joinResult.Danger.ContractingUnitName,
                                RelatedCompany = joinResult.Danger.RelatedCompany
                            })
                            .OrderByDescending(u => u.CheckDate)
                            .Page(input.PageNo, input.PageSize)
                            .ToListAsync();
                var models = MapperHelper.Map<List<QHSEOverLappingDangerModel>>(dangers);
                result = new PageResultModel<QHSEOverLappingDangerModel>
                {
                    TotalRows = totalRows,
                    Rows = models,
                    PageNo = input.PageNo,
                    PageSize = input.PageSize
                };

        }
        result.SetPage(input);
        result.CountTotalPage();

        return result;

    }
    public async Task<string> AddAsync(QHSEOverLappingDangerModel model)
    {
        using (var transaction = await _context.Database.BeginTransactionAsync())
        {
            try
            {   
                var hashList = new List<string>(); // 用于存储每个文件的哈希值
                if (model.DangerFiles.Count != 0)
                {
                    foreach (var file in model.DangerFiles)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    var dangermsg = await utilService.UploadFilesAsync( "DANGER",model.DangerFiles, model.UserId,_qhseFileService);
                    model.DangerPic = dangermsg.LastOrDefault().ArgumentName;
                }

                if (model.ReformerFiles.Count != 0)
                {
                    foreach (var file in model.ReformerFiles)
                    {
                        using (var sha256 = SHA256.Create())
                        {
                            byte[] byteArray = Convert.FromBase64String(file.Base64Content);
                            byte[] hash = sha256.ComputeHash(byteArray);
                            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();
                            hashList.Add(hashString);
                        }
                    }
                    var msg = await utilService.UploadFilesAsync("REFORMER",model.ReformerFiles, model.UserId, _qhseFileService);
                    model.ReformerPic = msg.LastOrDefault().ArgumentName;
                }
                model.ImageHash = string.Join("+", hashList); // 拼接哈希值字符串
                Guid guid = Guid.NewGuid();
                string guidString = RemoveHyphens(guid);
                if (string.IsNullOrEmpty(model.qhseXyDangerId))
                {
                    model.qhseXyDangerId = guidString;
                }
                model.ReformerUserId = "备用";
                model.ReformerUserName = "备用";
                if(model.DirectDepartment == null)
                {
                    model.DirectDepartment = "备用";
                }
                var danger = MapperHelper.Map<QHSESysOverLappingDanger>(model);
                await _context.AddAsync(danger);
                await _context.SaveChangesAsync();
                await _cacheService.ClearOverLappingCountCacheAsync();

                // Clear cache
                await _cacheService.ClearDangerCacheAsync();

                await transaction.CommitAsync(); // Commit the transaction

                if (!string.IsNullOrEmpty(model.qhseXyDangerId))
                {
                    return model.qhseXyDangerId;
                }
                else
                {
                    return null; // or throw an exception, depending on your requirements
                }
            }
            catch (Exception)
            {
                await transaction.RollbackAsync(); // Rollback the transaction if an exception occurs
                throw;
            }
        }
    }
    public static bool IsEmpty(object obj)
    {
        Type type = obj.GetType();
        PropertyInfo[] properties = type.GetProperties();

        foreach (PropertyInfo property in properties)
        {
            object value = property.GetValue(obj);
            var s = property.ToString();
            if (property.ToString() != "System.String QhseXyDangerId" && property.ToString() != "System.String ObserveComm" && property.ToString() != "System.String DangerPic" && property.ToString() != "System.String ReformerPic" && property.ToString() != "System.String CheckDescription"
                && property.ToString() != "System.Nullable`1[System.DateTime] AcceptanceDate" && property.ToString() != "System.String ImageHash" && (value == null || string.IsNullOrEmpty(value.ToString())))
            {
                return true;
            }
        }

        return false;
    }
    static string RemoveHyphens(Guid guid)
    {
        return guid.ToString().Replace("-", ""); // 通过 Replace 函数移除连字符 "-"
    }
    public async Task<bool> IsImageDuplicate(string newImageBase64)
    {
        if (string.IsNullOrEmpty(newImageBase64))
        {
            return false;
        }

        using (var sha256 = SHA256.Create())
        {
            byte[] byteArray = Convert.FromBase64String(newImageBase64);
            byte[] hash = sha256.ComputeHash(byteArray);
            string hashString = BitConverter.ToString(hash).Replace("-", "").ToLower();

            DateTime oneYearAgo = DateTime.Today.AddYears(-1);

            var duplicate = await _context.Set<QHSESysDanger>()
                .Where(danger => danger.CheckDate >= oneYearAgo && danger.ImageHash.Contains(hashString))
                .AnyAsync();

            return duplicate;
        }
    }
    public async Task<QHSEOverLappingDangerModel> QueryDetailAsync(string Did)
    {
        var danger = new QHSESysOverLappingDanger();
        danger = await _context.Set<QHSESysOverLappingDanger>().FindAsync(Did);
        var model = MapperHelper.Map<QHSEOverLappingDangerModel>(danger);
        if (model.DangerPic != null)
        {
            var dangerfiles = await utilService.GetFilesAsync(model.DangerPic, _qhseFileService);
            model.DangerFiles = dangerfiles;
        }
        if (model.ReformerPic != null)
        {
            var reformerfiles = await utilService.GetFilesAsync(model.ReformerPic, _qhseFileService);
            model.ReformerFiles = reformerfiles;
        }
        return model;
    }
    public async Task<int> DeleteAsync(IEnumerable<string> ids)
    {
        var dangers = await _context.Set<QHSESysOverLappingDanger>()
            .Where(a => ids.Contains(a.QhseXyDangerId))
            .ToListAsync();
        foreach (var danger in dangers)
        {
            if (danger.DangerPic != null)
            {
                List<string> namesDangerList = danger.DangerPic.Split(", ")
                                    .Select(name => name.Trim())  // 去除每个名字前后的空格
                                    .ToList();
                foreach (var name in namesDangerList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }
            if (danger.ReformerPic != null)
            {
                List<string> namesReformerList = danger.ReformerPic.Split(", ")
                                    .Select(name => name.Trim())  // 去除每个名字前后的空格
                                    .ToList();
                foreach (var name in namesReformerList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }
        }
        _context.RemoveRange(dangers);
        await _cacheService.ClearOverLappingCountCacheAsync();
        int ret = await _context.SaveChangesAsync();
        return ret;
    }
    public async Task<int> UpdateAsync(QHSEOverLappingDangerModel model)
    {
        var danger = await _context.Set<QHSESysOverLappingDanger>()
            .FindAsync(model.qhseXyDangerId);

        if (danger == null)
        {
            throw AppResultException.Status404NotFound("找不到隐患，更新失败");
        }
        var hashList = new List<string>(); // 用于存储每个文件的哈希值
        
        if (model.DangerFiles.Count != 0)
        {
            var fileUploadItems = model.DangerFiles.Select(file => new FileUploadItem
            {
                Name = file.Name,
                Base64Content = file.Base64Content
            }).ToList();
            var msgs = await utilService.UploadFilesAsync("DANGER", fileUploadItems, danger.UserId, _qhseFileService);
            bool uploadSuccess = msgs.All(msg => msg.FileStatus == 1);

            if (uploadSuccess)
            {
                if (!string.IsNullOrEmpty(danger.DangerPic))
                {
                    List<string> namesList = danger.DangerPic.Split(", ")
                                     .Select(name => name.Trim())  // 去除每个名字前后的空格
                                     .ToList();
                    foreach (var name in namesList)
                    {
                        _qhseFileService.RemoveAsync("ssew", name);
                    }
                }

                model.DangerPic = msgs.LastOrDefault()?.FileName;
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(danger.DangerPic))
            {
                List<string> namesList = danger.DangerPic.Split(", ")
                                        .Select(name => name.Trim())  // 去除每个名字前后的空格
                                        .ToList();
                foreach (var name in namesList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }

            model.DangerPic = null;
        }
        if (model.ReformerFiles.Count != 0)
        {
            var fileUploadItems = model.ReformerFiles.Select(file => new FileUploadItem
            {
                Name = file.Name,
                Base64Content = file.Base64Content
            }).ToList();
            var msgs = await utilService.UploadFilesAsync("REFORMER", fileUploadItems, danger.UserId, _qhseFileService);
            bool uploadSuccess = msgs.All(msg => msg.FileStatus == 1);

            if (uploadSuccess)
            {
                if (!string.IsNullOrEmpty(danger.ReformerPic))
                {
                    List<string> namesList = danger.ReformerPic.Split(", ")
                                     .Select(name => name.Trim())  // 去除每个名字前后的空格
                                     .ToList();
                    foreach (var name in namesList)
                    {
                        _qhseFileService.RemoveAsync("ssew", name);
                    }
                }

                model.ReformerPic = msgs.LastOrDefault()?.FileName;
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(danger.ReformerPic))
            {
                List<string> namesList = danger.ReformerPic.Split(", ")
                                        .Select(name => name.Trim())  // 去除每个名字前后的空格
                                        .ToList();
                foreach (var name in namesList)
                {
                    _qhseFileService.RemoveAsync("ssew", name);
                }
            }

            model.ReformerPic = null;
        }
        model.ImageHash = string.Join("+", hashList); // 拼接哈希值字符串
        MapperHelper.Map<QHSEOverLappingDangerModel, QHSESysOverLappingDanger>(model, danger);
        danger.DirectDepartment = "备用";
        _context.Update(danger);
        // 清缓存
        await _cacheService.ClearDangerCacheAsync();
        int ret = await _context.SaveChangesAsync();
        if (ret == 0)
        {
            throw AppResultException.Status200OK("更新记录数为0");
        }
        return ret;
    }
    public async Task<byte[]> GenerateExcel(OverLappingDangerPageInputModel model)
    {
        List<QHSEOverLappingDangerModel> models = new List<QHSEOverLappingDangerModel>();
        int totalPage = 0;

        // 获取总页数,一次查询1000条数据
        model.PageSize = 1000;
        PageResultModel<QHSEOverLappingDangerModel> pageResultModel = await GetPageAsync(model);
        totalPage = pageResultModel.TotalPage;

        for (int pageNo = 1; pageNo <= totalPage; pageNo++)
        {
            // 设置当前页码
            model.PageNo = pageNo;

            // 执行分页查询，获取当前页的数据
            pageResultModel = await GetPageAsync(model);
            List<QHSEOverLappingDangerModel> currentPageData = pageResultModel.Rows;

            // 将当前页的数据添加到总数据集合
            models.AddRange(currentPageData);
        }

        // 设置 ExcelPackage 的 LicenseContext
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var package = new ExcelPackage())
        {
            var worksheet = package.Workbook.Worksheets.Add("Sheet1");

            // 合并单元格
            ExcelRange titleCell = worksheet.Cells["A1:V1"];
            titleCell.Merge = true;
            // 设置标题内容
            titleCell.Value = model.TimeOn.Year.ToString()+"年"+model.TimeOn.Month.ToString() + "月" + "作业现场相关方隐患统计表";
            // 设置标题样式
            titleCell.Style.Font.Size = 20;
            titleCell.Style.Font.Bold = true;
            titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            // 设置表头并设置字体加粗
            for (int col = 0; col <= 21; col++)
            {
                ExcelRange headerCell = worksheet.Cells[2, col+1];
                headerCell.Value = GetHeaderTitle(col);
                headerCell.Style.Font.Bold = true;
            }

            // 填充数据
            for (int i = 0; i < models.Count; i++)
            {
                worksheet.Cells[i + 3, 1].Value = models[i].CheckDate.ToString();
                worksheet.Cells[i + 3, 2].Value = models[i].WorkItem;
                worksheet.Cells[i + 3, 3].Value = models[i].OrganizationName;
                worksheet.Cells[i + 3, 4].Value = models[i].Description;
                worksheet.Cells[i + 3, 5].Value = models[i].Status;
                worksheet.Cells[i + 3, 6].Value = models[i].DangerType;
                worksheet.Cells[i + 3, 7].Value = models[i].RelatedCompany;
                worksheet.Cells[i + 3, 8].Value = models[i].ContractingUnitName;
                // 填充更多属性值...
            }

            // 自动调整列宽
            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

            // 将 Excel 数据保存到字节数组
            var bytes = package.GetAsByteArray();

            return bytes;
        }
    }

    private string GetHeaderTitle(int column)
    {
        switch (column)
        {
            case 0:
                return "日期";
            case 1:
                return "井号（项目）";
            case 2:
                return "川庆公司所属单位";
            case 3:
                return "隐患情况";
            case 4:
                return "是否整改";
            case 5:
                return "隐患级别";
            case 6:
                return "隐患所属相关方公司";
            case 7:
                return "项目所属甲方处级发包单位";
            default:
                return string.Empty;
        }
    }
}
